package com.hanainfo.db;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.net.URL;
import java.net.URLConnection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.json.simple.JSONObject;
import org.json.simple.JSONValue;

import com.hanainfo.hanaApp.BallBean;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.ArrayAdapter;

public class DataBaseHelper extends SQLiteOpenHelper{
	private static final String MY_DB_NAME = "hanaApp.db";
	private static final String MY_TB_NAME = "test";
	private static final String HISTORY_TB_NAME = "HISTORY";
	
	public DataBaseHelper(Context context){
			super(context,MY_DB_NAME,null,5);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL("CREATE TABLE " + MY_TB_NAME + " (" + 
				"first VARCHAR," +
				"second VARCHAR," + 
				"third VARCHAR," + 
				"fourth VARCHAR," + 
				"fifth VARCHAR," + 
				"sixth VARCHAR);");
		db.execSQL("CREATE TABLE " + HISTORY_TB_NAME + " (" + 
				"GRWNO VARCHAR PRIMARY KEY," + //회차
				"FirstBall VARCHAR," + 
				"SecondBall VARCHAR," + 
				"ThirdBall VARCHAR," + 
				"FourthBall VARCHAR," +
				"FifthBall VARCHAR," +
				"SixthBall VARCHAR," +
				"BonusBall VARCHAR," +
				"GRWDate VARCHAR," + //추첨일
				"FirstPrzwnerCo VARCHAR," + //1등 당첨자 수
				"FirstWinamnt VARCHAR," + //1등 당첨금액
				"TotFirstWinamnt VARCHAR," + //총1등 당첨자금액
				"SecondPrzwnerCo VARCHAR," + //2등당첨자
				"SecondWinamnt VARCHAR," +
				"TotSecondWinamnt VARCHAR," +
				"ThirdPrzwnerCo VARCHAR," +
				"ThirdWinamnt VARCHAR," +
				"TotThirdWinamnt VARCHAR," +
				"ForthPrzwnerCo VARCHAR," +
				"ForthWinamnt VARCHAR," +
				"TotForthWinamnt VARCHAR," +
				"FifthPrzwnerCo VARCHAR," +
				"FifthWinamnt VARCHAR," +
				"TotFifthWinamnt VARCHAR," +
				"TotPrzwnerCo VARCHAR," + //총 당첨자수
				"TotPrzamnt VARCHAR," + 
				"TotSellamnt VARCHAR," +
				"curDRWNO VARCHAR," + // 현재 회차
				"prevDRWNO VARCHAR," +// 이전 회차
				"nextDRWNO VARCHAR" + // 다음 회차
				");");
		String spec = "http://www.645lotto.net/ajax_jsonNew.asp";	
	    URL url = null;
	    URLConnection con = null;
	    JSONObject job = null;
		try{
			url= new URL(spec);
			con = url.openConnection();
			con.setUseCaches(false);
			
			String api = "";
			String line = null;
			BufferedReader br = new BufferedReader(new InputStreamReader(con.getInputStream()));
			while ((line=br.readLine())!= null){
				api = api+line + "\n";
			}
			Object ob = JSONValue.parse(api);
			job = (JSONObject) ob;
			
		}catch (Exception e) {
			// TODO: handle exception
		}
		int lastNo = Integer.parseInt((String)job.get("GRWNO"));
		
		//처음 DB시작시 여태까지 모든 게임을 db에 저장해버린다.
		/*for(int i=1 ; i <= lastNo ; i++){
			spec = "http://www.645lotto.net/ajax_jsonNew.asp?sGameNo="+Integer.toString(i);	
		    url = null;
		    con = null;
		    job = null;
			try{
				url= new URL(spec);
				con = url.openConnection();
				con.setUseCaches(false);
				
				String api = "";
				String line = null;
				BufferedReader br = new BufferedReader(new InputStreamReader(con.getInputStream()));
				while ((line=br.readLine())!= null){
					api = api+line + "\n";
				}
				Object ob = JSONValue.parse(api);
				job = (JSONObject) ob;
				
			}catch (Exception e) {
				// TODO: handle exception
			}
			db.execSQL("INSERT INTO "+HISTORY_TB_NAME+" VALUES " +
					"('"+ job.get("GRWNO") + "' , "+
					"'"+ job.get("FirstBall") +"' , "+
					"'"+ job.get("SecondBall") +"' , "+
					"'"+ job.get("ThirdBall") +"' , "+
					"'"+ job.get("FourthBall") +"' , "+
					"'"+ job.get("FifthBall") +"' , "+
					"'"+ job.get("SixthBall") +"' , "+
					"'"+ job.get("BonusBall") +"' , "+
					"'"+ job.get("GRWDate") +"' , "+
					"'"+ job.get("FirstPrzwnerCo") +"' , "+
					"'"+ job.get("FirstWinamnt") +"' , "+
					"'"+ job.get("TotFirstWinamnt") +"' , "+
					"'"+ job.get("SecondPrzwnerCo") +"' , "+
					"'"+ job.get("SecondWinamnt") +"' , "+
					"'"+ job.get("TotSecondWinamnt") +"' , "+
					"'"+ job.get("ThirdPrzwnerCo") +"' , "+
					"'"+ job.get("ThirdWinamnt") +"' , "+
					"'"+ job.get("TotThirdWinamnt") +"' , "+
					"'"+ job.get("ForthPrzwnerCo") +"' , "+
					"'"+ job.get("ForthWinamnt") +"' , "+
					"'"+ job.get("TotForthWinamnt") +"' , "+
					"'"+ job.get("FifthPrzwnerCo") +"' , "+
					"'"+ job.get("FifthWinamnt") +"' , "+
					"'"+ job.get("TotFifthWinamnt") +"' , "+
					"'"+ job.get("TotPrzwnerCo") +"' , "+
					"'"+ job.get("TotPrzamnt") +"' , "+
					"'"+ job.get("TotSellamnt") +"' , "+
					"'"+ job.get("curDRWNO") +"' , "+
					"'"+ job.get("prevDRWNO") +"' , "+
					"'"+ job.get("nextDRWNO") +"')");
		}*/
		
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}
	
	public ArrayAdapter<BallBean> myBall(Context context){
		SQLiteOpenHelper mOpenHelper = new DataBaseHelper(context);
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();
		ArrayAdapter<BallBean> abc = new ArrayAdapter<BallBean>(context, android.R.layout.simple_list_item_1);
		Cursor cursor;
	    cursor=db.rawQuery("select *,rowid from test", null);
	    while(cursor.moveToNext()){
	    	BallBean myBall = new BallBean();
	    	myBall.setFirstBall(cursor.getString(0));
	    	//Log.d("--", cursor.getString(0));
	    	myBall.setSecondBall(cursor.getString(1));
	    	//Log.e("--", cursor.getString(1));
	    	myBall.setThirdBall(cursor.getString(2));
	    	//Log.d("--", cursor.getString(2));
	    	myBall.setFourthBall(cursor.getString(3));
	    	//Log.e("--", cursor.getString(3));
	    	myBall.setFifthBall(cursor.getString(4));
	    	//Log.d("--", cursor.getString(4));
	    	myBall.setSixthBall(cursor.getString(5));
	    	//Log.e("--", cursor.getString(5));
	    	myBall.setId(cursor.getString(6));
	    	//Log.e("--", cursor.getString(6));
	    	abc.add(myBall);
	    }
	    cursor.close();
	    db.close();
		return abc;
	}
	
	public BallBean lastBall(){
		JSONObject job = null;
		  //JSON URL 파싱 
		    String spec = "http://www.645lotto.net/ajax_jsonNew.asp";	
		    URL url = null;
		    URLConnection con = null;
		    String api = "";
			try{
				url= new URL(spec);
				con = url.openConnection();
				con.setUseCaches(false);
				
				String line = null;
				BufferedReader br = new BufferedReader(new InputStreamReader(con.getInputStream()));
				while ((line=br.readLine())!= null){
					api = api+line + "\n";
				}
				Object ob = JSONValue.parse(api);
				 job = (JSONObject) ob;
				
			}catch (Exception e) {
				// TODO: handle exception
			}
			BallBean ballBean = new BallBean(); // 만들고 보니 굳이 빈이 필요가 없는듯.....
			ballBean.setFirstBall((String)job.get("FirstBall"));
			ballBean.setSecondBall((String)job.get("SecondBall"));
			ballBean.setThirdBall((String)job.get("ThirdBall"));
			ballBean.setFourthBall((String)job.get("FourthBall"));
			ballBean.setFifthBall((String)job.get("FifthBall"));
			ballBean.setSixthBall((String)job.get("SixthBall"));
			ballBean.setGrwno((String)job.get("Grwno"));
			ballBean.setGrwDate((String)job.get("GrwDate"));
		return ballBean;
	}
}
